#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
将response.json数据导入到SQL Server数据库的脚本
"""

import json
import pymssql
from datetime import datetime

# 数据库配置
DB_CONFIG = {
    'host': '192.168.0.248',
    'port': 1433,
    'user': 'feng',
    'password': '0400923',
    'database': 'ERPDB4'
}

# 表名
TABLE_NAME = 'response_data'

def connect_to_database():
    """连接到SQL Server数据库"""
    try:
        conn = pymssql.connect(
            server=DB_CONFIG['host'],
            port=DB_CONFIG['port'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password'],
            database=DB_CONFIG['database'],
            charset='utf8'
        )
        print(f"成功连接到数据库 {DB_CONFIG['database']}")
        return conn
    except Exception as e:
        print(f"连接数据库失败: {e}")
        return None

def create_table(cursor):
    """创建数据表"""
    create_table_sql = f"""
    IF OBJECT_ID('{TABLE_NAME}', 'U') IS NOT NULL
        DROP TABLE {TABLE_NAME}
    
    CREATE TABLE {TABLE_NAME} (
        id INT IDENTITY(1,1) PRIMARY KEY,
        station_Code_Back NVARCHAR(50),
        station_Name_Back NVARCHAR(100),
        station_Code_Receive NVARCHAR(50),
        station_Name_Receive NVARCHAR(100),
        station_Code_Repair NVARCHAR(50),
        station_Name_Repair NVARCHAR(100),
        station_Code_Recheck NVARCHAR(50),
        station_Name_Recheck NVARCHAR(100),
        cause_code NVARCHAR(10),
        workbill_code NVARCHAR(100),
        handleMethod INT,
        operation_Code_Back NVARCHAR(100),
        operation_Code_Receive NVARCHAR(100),
        operation_Code_Recheck NVARCHAR(100),
        operation_Code_Repair NVARCHAR(100),
        TagCode NVARCHAR(100),
        employee_Code_Back NVARCHAR(50),
        employee_Name_Back NVARCHAR(100),
        operation_Name_Back NVARCHAR(200),
        Date_Back DATETIME,
        employee_Code_Receive NVARCHAR(50),
        employee_Name_Receive NVARCHAR(100),
        operation_Name_Receive NVARCHAR(200),
        employee_Code_Repair NVARCHAR(50),
        employee_Name_Repair NVARCHAR(100),
        operation_Name_Repair NVARCHAR(200),
        Date_Repair DATETIME,
        employee_Code_Recheck NVARCHAR(50),
        employee_Name_Recheck NVARCHAR(100),
        operation_Name_Recheck NVARCHAR(200),
        Date_Recheck DATETIME,
        BindQuantity INT,
        cause_name NVARCHAR(100),
        style_Code NVARCHAR(100),
        style_Name NVARCHAR(200),
        color_Code NVARCHAR(100),
        color_Name NVARCHAR(200),
        size_Code NVARCHAR(50),
        size_Name NVARCHAR(100),
        part_Code NVARCHAR(50),
        part_Name NVARCHAR(100),
        created_at DATETIME DEFAULT GETDATE()
    )
    """
    
    try:
        cursor.execute(create_table_sql)
        print(f"成功创建表 {TABLE_NAME}")
        return True
    except Exception as e:
        print(f"创建表失败: {e}")
        return False

def parse_datetime(date_str):
    """解析日期时间字符串"""
    if not date_str:
        return None
    try:
        return datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S')
    except ValueError:
        return None

def import_data(conn, cursor, json_file_path):
    """导入JSON数据到数据库"""
    try:
        # 读取JSON文件
        with open(json_file_path, 'r', encoding='utf-8') as f:
            data = json.load(f)
        
        if 'data' not in data:
            print("JSON文件格式不正确，缺少'data'字段")
            return False
        
        records = data['data']
        print(f"共找到 {len(records)} 条记录")
        
        # 准备插入语句
        insert_sql = f"""
        INSERT INTO {TABLE_NAME} (
            station_Code_Back, station_Name_Back, station_Code_Receive, station_Name_Receive,
            station_Code_Repair, station_Name_Repair, station_Code_Recheck, station_Name_Recheck,
            cause_code, workbill_code, handleMethod, operation_Code_Back, operation_Code_Receive,
            operation_Code_Recheck, operation_Code_Repair, TagCode, employee_Code_Back,
            employee_Name_Back, operation_Name_Back, Date_Back, employee_Code_Receive,
            employee_Name_Receive, operation_Name_Receive, employee_Code_Repair,
            employee_Name_Repair, operation_Name_Repair, Date_Repair, employee_Code_Recheck,
            employee_Name_Recheck, operation_Name_Recheck, Date_Recheck, BindQuantity,
            cause_name, style_Code, style_Name, color_Code, color_Name, size_Code,
            size_Name, part_Code, part_Name
        ) VALUES (
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %s, %s, %s, %s, %s, %s, %s, %s, %s,
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %s, %s, %s, %s, %s, %s, %s, %s, %s
        )
        """
        
        # 批量插入数据
        inserted_count = 0
        for record in records:
            try:
                # 处理数据
                values = (
                    record.get('station_Code_Back'),
                    record.get('station_Name_Back'),
                    record.get('station_Code_Receive'),
                    record.get('station_Name_Receive'),
                    record.get('station_Code_Repair'),
                    record.get('station_Name_Repair'),
                    record.get('station_Code_Recheck'),
                    record.get('station_Name_Recheck'),
                    record.get('cause_code'),
                    record.get('workbill_code'),
                    record.get('handleMethod', 0),
                    record.get('operation_Code_Back'),
                    record.get('operation_Code_Receive'),
                    record.get('operation_Code_Recheck'),
                    record.get('operation_Code_Repair'),
                    record.get('TagCode'),
                    record.get('employee_Code_Back'),
                    record.get('employee_Name_Back'),
                    record.get('operation_Name_Back'),
                    parse_datetime(record.get('Date_Back')),
                    record.get('employee_Code_Receive'),
                    record.get('employee_Name_Receive'),
                    record.get('operation_Name_Receive'),
                    record.get('employee_Code_Repair'),
                    record.get('employee_Name_Repair'),
                    record.get('operation_Name_Repair'),
                    parse_datetime(record.get('Date_Repair')),
                    record.get('employee_Code_Recheck'),
                    record.get('employee_Name_Recheck'),
                    record.get('operation_Name_Recheck'),
                    parse_datetime(record.get('Date_Recheck')),
                    record.get('BindQuantity', 0),
                    record.get('cause_name'),
                    record.get('style_Code'),
                    record.get('style_Name'),
                    record.get('color_Code'),
                    record.get('color_Name'),
                    record.get('size_Code'),
                    record.get('size_Name'),
                    record.get('part_Code'),
                    record.get('part_Name')
                )
                
                cursor.execute(insert_sql, values)
                inserted_count += 1
                
                # 每1000条记录提交一次事务
                if inserted_count % 1000 == 0:
                    conn.commit()
                    print(f"已插入 {inserted_count} 条记录")
                    
            except Exception as e:
                print(f"插入记录时出错: {e}")
                print(f"出错记录: {record}")
                continue
        
        # 提交剩余的记录
        conn.commit()
        print(f"数据导入完成，共插入 {inserted_count} 条记录")
        return True
        
    except Exception as e:
        print(f"导入数据时出错: {e}")
        return False

def main():
    """主函数"""
    print("开始导入JSON数据到SQL Server数据库...")
    
    # 连接数据库
    conn = connect_to_database()
    if not conn:
        return
    
    try:
        cursor = conn.cursor()
        
        # 创建表
        if not create_table(cursor):
            return
        
        # 导入数据
        json_file_path = 'response.json'
        if import_data(conn, cursor, json_file_path):
            print("数据导入成功完成!")
        else:
            print("数据导入失败!")
            
    except Exception as e:
        print(f"执行过程中出错: {e}")
    finally:
        if conn:
            conn.close()
            print("数据库连接已关闭")

if __name__ == "__main__":
    main()